package web.dao;

import web.bean.Constants;
import web.bean.District;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2016/7/7.
 */
@Repository
public class LocationDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询省份
     * @return
     */
    public Map<String,Object> findLocation(){
        String sql="select code,`name` from district where code <> ''";
        final Map<String,Object> map=new HashMap<String, Object>();;
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                map.put(rs.getString("code"),rs.getString("name"));
            }
        });
        return map;
    }

    /**
     * 查询省级地域
     * key:name
     * value:code
     * @return
     */
    public Map<String,Object> findLocationByName(){
        String sql="select code,`name` from district where parentid < 35 and  code <> ''";
        final Map<String,Object> map=new HashMap<String, Object>();;
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                if(map.get("name")==null) {
                    map.put(rs.getString("name"), rs.getString("code"));
                }
            }
        });
        return map;
    }


    /**
     * 查询省份
     * @return
     */
    public List<Map<String,Object>> findAllLocation(){
        String sql="select code,`name` from district where parentid = 0 and code <> ''";
        final List<Map<String,Object>> list=new ArrayList<Map<String, Object>>();
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Map<String,Object> map=new HashMap<String, Object>();
                map.put("name",rs.getString("name"));
                map.put("code",rs.getString("code"));
                list.add(map);
            }
        });
        return list;
    }
    /**
     * 查询省份
     * @return
     */
    public List<String> findAllLocationList(){
        String sql="select code from district where parentid = 0 and code <> ''";
        final List<String>list=new ArrayList<String>();
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
               list.add(rs.getString("code"));
            }
        });
        return list;
    }


    /**
     * 通过上级Id查询数据
     * @param id
     * @return
     */
    public List<District> getLocationList1(int id){
        String sql="select id,name,parentid,code from "+ Constants.DISTRICT+" where parentid=? and code!=0";
        final List<District> list=new ArrayList<District>();
        jdbcTemplate.query(sql,new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                District district=new District();
                district.setId(rs.getInt("id"));
                district.setName(rs.getString("name"));
                district.setParentid(rs.getInt("parentid"));
                district.setCode(rs.getString("code"));
                list.add(district);
            }
        },id);
        return list;
    }

    public Map<Integer,Integer> findParent(){
        String sql="select id,code from "+Constants.DISTRICT+" where parentid=0 and code!=0";
        final  Map<Integer,Integer> map=new HashMap<Integer, Integer>();
        jdbcTemplate.query(sql,new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                map.put(rs.getInt("code"),rs.getInt("id"));
            }
        });
        return map;
    }

    /**
     * 通过城市查到对应的省份
     * @param cityCode
     * @return
     */
    public int findProvinceByCity(int cityCode ){
        String sql="SELECT code FROM "+Constants.DISTRICT+" WHERE id=(SELECT parentid FROM "+Constants.DISTRICT+" WHERE code=?)";
        return jdbcTemplate.queryForObject(sql, Integer.class,cityCode);
    }

    public  Map<Integer,Integer> getALLParentByCity (){
        final  Map<Integer,Integer> map=new HashMap<Integer, Integer>();
        String sql="select parentid,code from "+Constants.DISTRICT+" where parentid=0 and code!=0";
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                map.put(rs.getInt("code"),rs.getInt("parentid"));
            }
        });
        return map;
    }
    public  Map<Integer,Integer> getAllCodeById (){
        final  Map<Integer,Integer> map=new HashMap<Integer, Integer>();
        String sql=" SELECT id,code FROM "+Constants.DISTRICT+" where code!=0";
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                map.put(rs.getInt("id"),rs.getInt("code"));
            }
        });
        return map;
    }

    /**
     * 通过IP查询对应的 地域名称
     * @param ip
     */
    public String findProvinceByIp(long ip){
        String sql=" SELECT province FROM ip2city WHERE start_ip<=? AND end_ip>=?  ";
        String province="";
        try{
            province= jdbcTemplate.queryForObject(sql,String.class,ip,ip);
        } catch (Exception e) {
            province="";
        }
        return province;
    }

}
